"""
Case Type   : 基础功能-SQL语法-DDL-ALTER TABLE PARTITION
Case Name   : Interval分区表执行drop partition,并行对该分区update
Create At   : 2022/05/20
Owner       : kyeleze
Description :
    1.创建interval分区表并插入数据
    2.建立连接db1,开始事务drop partition;建立连接db2,启动线程开始事务同分区update
    3.等待2秒,查看数据库连接状态,db2第一个事务加锁导致处于busy状态
    4.db1执行rollback;查看db2状态,db2执行rollback
Expect      :
    1.成功
    2.成功
    3.db2连接处于busy状态
    4.db1执行rollback成功;db2状态变为idle,执行rollback成功
History     :
"""

import os
import unittest
import time

import py_opengauss
from yat.test import Node

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread


class IntervalPartitionCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_interval_table_0129'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        self.con_info = f'opengauss://' \
                        f'{self.user_node.db_user}:' \
                        f'{self.user_node.db_password}@' \
                        f'{self.user_node.db_host}:' \
                        f'{self.user_node.db_port}/' \
                        f'{self.user_node.db_name}'

    def test_partition_ddl_dml(self):
        step = '----step1:创建interval分区表并插入数据;expect:成功----'
        self.log.info(step)
        sql_cmd = f'''drop table if exists {self.table_name};
                create table {self.table_name}
                (prod_id NUMBER(6),
                 time_id DATE,
                 channel_id CHAR(10))
                partition by range (time_id)
                interval('1 day')
                ( partition p1 values less than ('2019-02-01 00:00:00'),
                  partition p2 values less than ('2019-02-02 00:00:00'));
                insert into {self.table_name} 
                values(1, '2019-01-31 00:00:00', 'a'),
                (2, '2019-02-1 00:00:00', 'b');
                '''
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                      "执行失败:" + step)

        step = '----step2:建立连接db1,开始事务drop partition;' \
               '建立连接db2,启动线程开始事务同分区update;expect:成功----'
        self.log.info(step)
        self.db1 = py_opengauss.open(self.con_info)
        begin_result = self.db1.query('begin;')
        self.log.info(begin_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        self.db2 = py_opengauss.open(self.con_info)
        begin_result = self.db2.query('begin;')
        self.log.info(begin_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        ddl_sql = f'alter table {self.table_name} drop partition p1;'
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result, "执行失败:" + step)
        dml_sql = f"update {self.table_name} set channel_id='c' " \
                  f"where time_id='2019-01-31 00:00:00';"
        thread = ComThread(self.db2.query, args=(dml_sql,))
        thread.setDaemon(True)
        thread.start()

        step = '----step3:等待2秒,查看数据库连接状态,' \
               'db2第一个事务加锁导致处于busy状态;expect:db2连接处于busy状态----'
        self.log.info(step)
        time.sleep(2)
        db1_state = self.db1.state
        db2_state = self.db2.state
        self.log.info(db1_state)
        self.log.info(db2_state)
        self.assertIn('idle in block', db1_state, '执行失败' + step)
        self.assertIn('busy', db2_state, '执行失败' + step)

        step = '--step4:db1执行rollback;查看db2状态,db2执行rollback ' \
               'expect:db1执行rollback成功;db2状态变为idle,执行rollback成功--'
        self.log.info(step)
        result = self.db1.query('rollback')
        self.log.info(result)
        self.assertIn(self.constant.ROLLBACK_MSG, result, '执行失败' + step)
        time.sleep(1)
        db2_state = self.db2.state
        self.log.info(db2_state)
        self.assertIn('idle in block', db2_state, '执行失败' + step)
        result = self.db2.query('rollback')
        self.log.info(result)
        self.assertIn(self.constant.ROLLBACK_MSG, result, '执行失败' + step)

    def tearDown(self):
        step = '--恢复环境,关闭session,删除测试表--'
        self.log.info(step)
        self.db1.close()
        self.db2.close()
        result = self.primary_sh.execut_db_sql(f'drop table {self.table_name};')
        self.log.info(result)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.log.info(f'-----{os.path.basename(__file__)} end--')
